using System;
using System.Collections.Generic;
using System.Text;
using FinanceStats.Entities;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data;
using FinanceStats.Common;
using System.Data.Common;

namespace FinanceStats.DAL
{
    public static class OutcomeGroupDAO
    {
        private static string insertSQL = "INSERT INTO ACS.DBO.OutcomeGroup (GroupName) VALUES (@GroupName) SET @GroupID = SCOPE_IDENTITY()";
        private static string updateSQL = "UPDATE ACS.DBO.OutcomeGroup SET GroupName = @GroupName WHERE GroupID = @GroupID";
        private static string selectSQLTotal = @"SELECT {0} G1.GROUPID, G1.GROUPNAME,  SUM(O1.AMOUNT) AS TOTAL FROM ACS.DBO.OUTCOMEGROUP G1 
JOIN ACS.DBO.OUTCOMEITEM I1 ON G1.GROUPID = I1.GROUPID
JOIN ACS.DBO.USEROUTCOME O1 ON I1.ITEMID = O1.ITEMID
WHERE O1.DATE > @StartDate AND O1.DATE < @EndDate
GROUP BY G1.GROUPID, G1.GROUPNAME ORDER BY SUM(O1.AMOUNT) DESC";
        
        private static DbCommand insertCommand = null;
        private static DbCommand updateCommand = null;
        private static DbCommand selecTotalCommand = null;
        
        private static DbCommand GetInsertCommand(SqlDatabase db)
        {
            if (insertCommand == null)
            {
                insertCommand = db.GetSqlStringCommand(insertSQL);
                db.AddInParameter(insertCommand, "@GroupName", DbType.String);
                db.AddOutParameter(insertCommand, "@GroupID", DbType.Int32, 4);
            }
            return insertCommand;
        }

        private static DbCommand GetUpdateCommand(SqlDatabase db)
        {
            if (updateCommand == null)
            {
                updateCommand = db.GetSqlStringCommand(updateSQL);
                db.AddInParameter(updateCommand, "@GroupName", DbType.String);
                db.AddInParameter(updateCommand, "@GroupID", DbType.Int32);
            }
            return updateCommand;
        }

        private static DbCommand GetSelectTotalCommand(SqlDatabase db, string top)
        {
            selecTotalCommand = db.GetSqlStringCommand(String.Format(selectSQLTotal, top));
            db.AddInParameter(selecTotalCommand, "@StartDate", DbType.DateTime);
            db.AddInParameter(selecTotalCommand, "@EndDate", DbType.DateTime);
            return selecTotalCommand;
        }

        private static void SetInsertValues(SqlDatabase db, DbCommand cmd, OutcomeGroup og)
        {
            db.SetParameterValue(cmd, "@GroupName", og.GroupName);
        }

        private static void SetUpdateValues(SqlDatabase db, DbCommand cmd, OutcomeGroup og)
        {
            db.SetParameterValue(cmd, "@GroupID", (int)og.GroupID);
            db.SetParameterValue(cmd, "@GroupName", og.GroupName);
        }
        
        public static List<OutcomeGroup> LoadOutcomeGroups()
        {
            List<OutcomeGroup> res = new List<OutcomeGroup>();
            string sql = "SELECT GroupID, GroupName FROM ACS.DBO.OutcomeGroup ORDER BY GroupName";
            IDataReader reader = null;
            try
            {
                SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.ACSConnectionString));
                reader = db.ExecuteReader(CommandType.Text, sql);
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        OutcomeGroup og = new OutcomeGroup();
                        og.GroupID = reader.GetInt32(0);
                        og.GroupName = reader.IsDBNull(1) ? String.Empty : reader.GetString(1);
                        res.Add(og);
                    }
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                res = null;
                throw ex;
            }
            return res;
        }

        public static List<OutcomeGroup> LoadOutcomeGroups(string top, DateTime startDate, DateTime endDate)
        {
            List<OutcomeGroup> res = new List<OutcomeGroup>();
            IDataReader reader = null;
            try
            {
                SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.ACSConnectionString));
                DbCommand cmd = GetSelectTotalCommand(db, top);
                db.SetParameterValue(cmd, "@StartDate", startDate);
                db.SetParameterValue(cmd, "@EndDate", endDate);
                reader = db.ExecuteReader(cmd);
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        OutcomeGroup og = new OutcomeGroup();
                        og.GroupID = reader.GetInt32(0);
                        og.GroupName = reader.GetString(1);
                        og.GroupTotal = reader.GetDecimal(2);
                        res.Add(og);
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                res = null;
                throw ex;
            }
            return res;
        }

        public static bool InsertOutcomeGroup(OutcomeGroup og)
        {
            bool res = false;
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.ACSConnectionString));
            DbCommand cmd = GetInsertCommand(db);
            SetInsertValues(db, cmd, og);
            res = Convert.ToBoolean(db.ExecuteNonQuery(cmd));
            if (res)
            {
                og.GroupID = (int)db.GetParameterValue(cmd, "@GroupID");
            }
            return res;
        }

        public static bool UpdateOutcomeGroup(OutcomeGroup og)
        {
            bool res = false;
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.ACSConnectionString));
            DbCommand cmd = GetUpdateCommand(db);
            SetUpdateValues(db, cmd, og);
            res = Convert.ToBoolean(db.ExecuteNonQuery(cmd));
            return res;
        }
    }
}
